package com.typingtutor.dto;

import com.typingtutor.db.DatabaseManager;
import com.typingtutor.model.TypingData;
import lombok.Data;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

@Data
public class TypingDataDAO {
    private Map<String, LinkedHashMap<String, Integer>> correctCountByDate;
    private Map<String, LinkedHashMap<String, Integer>> errorCountByDate;
    private static volatile TypingDataDAO INSTANCE;
    private final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    private final List<TypingData> cacheRecords = new ArrayList<>(); // 内存缓存，暂存未提交数据

    // 私有构造函数，防止实例化
    private TypingDataDAO() {
        correctCountByDate = new HashMap<>();
        errorCountByDate = new HashMap<>();
        loadDataFromDatabase(); // 初始化时加载数据库数据
    }

    // 单例模式（线程安全）
    public static TypingDataDAO getInstance() {
        if (INSTANCE == null) {
            synchronized (TypingDataDAO.class) {
                if (INSTANCE == null) {
                    INSTANCE = new TypingDataDAO();
                }
            }
        }
        return INSTANCE;
    }

    /**
     * 从数据库加载数据到内存缓存
     */
    private void loadDataFromDatabase() {
        try (Connection conn = DatabaseManager.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM typing_data")) {

            while (rs.next()) {
                String date = rs.getString("date");
                String displayName = rs.getString("display_name");
                int correctCount = rs.getInt("correct_count");
                int errorCount = rs.getInt("error_count");

                // 更新内存中的统计数据
                correctCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                        .put(displayName, correctCount);

                errorCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                        .put(displayName, errorCount);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 记录输入数据（统一处理正确/错误）
     * @param inputContent 用户输入内容（错误时不为空）
     * @param correctAnswer 正确答案
     * @param type 类型（correct/error）
     */
    public void recordData(String inputContent, String correctAnswer, String type) {
        String date = dateFormat.format(new Date());
        String displayName = correctAnswer; // 使用正确答案作为显示名称

        try (Connection conn = DatabaseManager.getConnection()) {
            // 先检查是否已有该日期和显示名称的记录
            String checkSql = "SELECT id, correct_count, error_count FROM typing_data " +
                    "WHERE `date` = ? AND display_name = ?";

            try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
                checkStmt.setString(1, date);
                checkStmt.setString(2, displayName);

                try (ResultSet rs = checkStmt.executeQuery()) {
                    if (rs.next()) {
                        // 记录已存在，更新计数
                        String updateSql = "UPDATE typing_data SET " +
                                "correct_count = correct_count + ?, " +
                                "error_count = error_count + ? " +
                                "WHERE id = ?";

                        try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                            updateStmt.setInt(1, "correct".equals(type) ? 1 : 0);
                            updateStmt.setInt(2, "error".equals(type) ? 1 : 0);
                            updateStmt.setString(3, rs.getString("id"));
                            updateStmt.executeUpdate();

                            // 更新内存缓存
                            correctCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                                    .put(displayName, rs.getInt("correct_count") + ("correct".equals(type) ? 1 : 0));

                            errorCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                                    .put(displayName, rs.getInt("error_count") + ("error".equals(type) ? 1 : 0));
                        }
                    } else {
                        // 记录不存在，插入新记录
                        String insertSql = "INSERT INTO typing_data (id, `date`, display_name, correct_count, error_count) " +
                                "VALUES (?, ?, ?, ?, ?)";

                        try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                            insertStmt.setString(1, UUID.randomUUID().toString());
                            insertStmt.setString(2, date);
                            insertStmt.setString(3, displayName);
                            insertStmt.setInt(4, "correct".equals(type) ? 1 : 0);
                            insertStmt.setInt(5, "error".equals(type) ? 1 : 0);
                            insertStmt.executeUpdate();

                            // 更新内存缓存
                            correctCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                                    .put(displayName, "correct".equals(type) ? 1 : 0);

                            errorCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                                    .put(displayName, "error".equals(type) ? 1 : 0);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取所有错误记录（从数据库查询）
     * @return 错误记录列表
     */
    public List<TypingData> getErrorRecords() {
        List<TypingData> results = new ArrayList<>();
        String sql = "SELECT display_name, error_count, `date` FROM typing_data " +
                "WHERE error_count > 0 ORDER BY `date` DESC";

        try (Connection conn = DatabaseManager.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                TypingData data = new TypingData();
                data.setInputContent("错误次数: " + rs.getInt("error_count"));
                data.setCorrectAnswer(rs.getString("display_name"));
                data.setRecordTime(rs.getString("date"));
                data.setType("error");
                results.add(data);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return results;
    }

    /**
     * 获取所有正确记录（从数据库查询）
     * @return 正确记录列表
     */
    public List<TypingData> getCorrectRecords() {
        List<TypingData> results = new ArrayList<>();
        String sql = "SELECT display_name, correct_count, `date` FROM typing_data " +
                "WHERE correct_count > 0 ORDER BY `date` DESC";

        try (Connection conn = DatabaseManager.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                TypingData data = new TypingData();
                data.setInputContent("正确次数: " + rs.getInt("correct_count"));
                data.setCorrectAnswer(rs.getString("display_name"));
                data.setRecordTime(rs.getString("date"));
                data.setType("correct");
                results.add(data);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return results;
    }

    /**
     * 获取今日统计数据
     * @return 今日正确数和错误数
     */
    public Map<String, Integer> getTodayStatistics() {
        String today = dateFormat.format(new Date());
        Map<String, Integer> result = new HashMap<>();
        result.put("correct", getCorrectCountByDate(today).values().stream().mapToInt(Integer::intValue).sum());
        result.put("error", getErrorCountByDate(today).values().stream().mapToInt(Integer::intValue).sum());
        return result;
    }

    /**
     * 按类型和日期统计记录数
     * @param type 记录类型（correct/error）
     * @param date 日期（格式：yyyy-MM-dd）
     * @return 记录数量
     */
    private int countByTypeAndDate(String type, String date) {
        LinkedHashMap<String, Integer> map = "correct".equals(type)
                ? getCorrectCountByDate(date)
                : getErrorCountByDate(date);
        return map.values().stream().mapToInt(Integer::intValue).sum();
    }

    /**
     * 获取所有日期
     */
    public Set<String> getAllDates() {
        Set<String> allDates = new HashSet<>();
        allDates.addAll(correctCountByDate.keySet());
        allDates.addAll(errorCountByDate.keySet());
        return allDates;
    }

    /**
     * 获取某天的最大统计值（用于设置图表范围）
     */
    public int getMaxCount(String date) {
        int max = 0;
        Map<String, Integer> correctMap = getCorrectCountByDate(date);
        for (int count : correctMap.values()) {
            max = Math.max(max, count);
        }
        Map<String, Integer> errorMap = getErrorCountByDate(date);
        for (int count : errorMap.values()) {
            max = Math.max(max, count);
        }
        return max + 1;
    }

    /**
     * 按值对 Map 进行排序
     */
    private Map<String, Integer> sortMapByValue(Map<String, Integer> map) {
        List<Map.Entry<String, Integer>> list = new ArrayList<>(map.entrySet());
        list.sort(Map.Entry.comparingByValue());

        LinkedHashMap<String, Integer> sortedMap = new LinkedHashMap<>();
        for (Map.Entry<String, Integer> entry : list) {
            sortedMap.put(entry.getKey(), entry.getValue());
        }
        return sortedMap;
    }

    /**
     * 获取某天的正确次数统计
     */
    public LinkedHashMap<String, Integer> getCorrectCountByDate(String date) {
        return correctCountByDate.getOrDefault(date, new LinkedHashMap<>());
    }

    /**
     * 获取某天的错误次数统计
     */
    public LinkedHashMap<String, Integer> getErrorCountByDate(String date) {
        return errorCountByDate.getOrDefault(date, new LinkedHashMap<>());
    }

    /**
     * 更新统计数据（通常由数据库加载时调用）
     */
    public void updateStatistics(String key, String date, int correctCount, int errorCount) {
        correctCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                .put(key, correctCount);

        errorCountByDate.computeIfAbsent(date, k -> new LinkedHashMap<>())
                .put(key, errorCount);
    }
}